from fastapi import FastAPI, Request
from fastapi.templating import Jinja2Templates

from dblock import dblock

app = FastAPI()
templates = Jinja2Templates(directory="./")


@app.get("/")
async def list_grade(request: Request):
    with dblock() as db:
        db.execute("""
        SELECT s.name as stu_name, c.name as course_name, g.grade 
        FROM course_grade as g
        INNER JOIN student as s ON g.stu_sn = s.sn
        INNER JOIN course as c  ON g.cou_sn = c.sn ;
        """)

        # items = [tuple(row) for row in db]
        items = [row for row in db]

    return templates.TemplateResponse(
        "grade_list.html",
        {
            "request": request,
            "items": items,
        },
    )
